using Lzfy_His_Service.Models;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Dapper;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 患者卡信息查询服务实现
    /// </summary>
    public class PatientCardInfoService : IPatientCardInfoService
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<PatientCardInfoService> _logger;

        public PatientCardInfoService(
            IOracleConnectionFactory oracleConnectionFactory,
            ILogger<PatientCardInfoService> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 查询患者卡信息
        /// </summary>
        /// <param name="request">查询请求</param>
        /// <returns>患者卡信息列表</returns>
        public async Task<List<PatientCardInfo>> GetPatientCardInfoAsync(PatientCardQueryRequest request)
        {
            try
            {
                _logger.LogInformation("开始查询患者卡信息，姓名: {Name}, 手机号: {Phone}, 证件号: {IdNumber}", 
                    request.Name, request.Phone, request.IdNumber);

                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT t1.patient_id as PatientId,
                           t1.name as Name,
                           decode(t1.sex_code, '1', '男', '2', '女', '未知') as Sex,
                           to_char(t1.date_of_birth, 'yyyy-mm-dd') as DateOfBirth,
                           t1.id_number as IdNumber,
                           to_char(t1.create_time, 'yyyy-mm-dd hh24:mi:ss') as CreateTime,
                           t3.balance as Balance,
                           t1.phone_number1 as PhoneNumber1,
                           t1.guardian_addr as GuardianAddr,
                           t1.contact_addr_street as ContactAddrStreet
                      FROM pca.pca_patient_info t1
                      JOIN pca.pca_prepay_account_index t2 
                        ON t1.patient_id = t2.patient_id
                      JOIN pca.pca_prepay_account_master t3 
                        ON t2.account_index = t3.account_index
                       AND t3.account_type = 'O'
                     WHERE (TRIM(:name) = '' OR TRIM(:name) IS NULL OR t1.name = TRIM(:name))
                       AND (TRIM(:phone) = '' OR TRIM(:phone) IS NULL OR t1.phone_number1 = TRIM(:phone))
                       AND (TRIM(:idNumber) = '' OR TRIM(:idNumber) IS NULL OR t1.id_number = TRIM(:idNumber))";

                var parameters = new
                {
                    name = string.IsNullOrWhiteSpace(request.Name) ? "" : request.Name.Trim(),
                    phone = string.IsNullOrWhiteSpace(request.Phone) ? "" : request.Phone.Trim(),
                    idNumber = string.IsNullOrWhiteSpace(request.IdNumber) ? "" : request.IdNumber.Trim()
                };

                var result = await connection.QueryAsync<PatientCardInfo>(sql, parameters);
                var patientCardInfoList = result.ToList();

                _logger.LogInformation("患者卡信息查询完成，查询到 {Count} 条记录", patientCardInfoList.Count);

                return patientCardInfoList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "患者卡信息查询失败，姓名: {Name}, 手机号: {Phone}, 证件号: {IdNumber}", 
                    request.Name, request.Phone, request.IdNumber);
                throw;
            }
        }
    }
}